/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.uoc.tdp.dal;

import edu.uoc.tdp.entity.Booking;
import edu.uoc.tdp.customException.EIssueException;

import edu.uoc.tdp.entity.Payment;
import edu.uoc.tdp.i18n.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.text.SimpleDateFormat;
import java.util.Calendar;

/**
 * @author tomeu
 */
public class ManagementBookings {

    private ManagementDb mngBD = null;
    private Connection conexion = null;

    public ManagementBookings() throws EIssueException {
        mngBD = new ManagementDb();
        try {
            mngBD.connectDB();
        } catch (edu.uoc.tdp.common.EIssueException ex) {
            Logger.getLogger(ManagementBookings.class.getName()).log(Level.SEVERE, null, ex);
        }
        conexion = mngBD.getConnection();
    }

    public ManagementBookings(ManagementDb mngDb) {
        mngBD = mngDb;
        conexion = mngDb.getConnection();
    }

    /**
     * Mètode que obte la lista de reserves - taula Bookings
     * @return Lista de reserves
     */
    public List<Booking> GetAllBookings() throws EIssueException {
        List<Booking> listBooking = new ArrayList();

        try {
            Statement command = this.mngBD.createCommand();
            String select = "SELECT * FROM bookings";
            ResultSet rs = command.executeQuery(select);
            while (rs.next()) {
                listBooking.add(new Booking(rs.getInt("bookingid"), rs.getString("userid"), rs.getInt("spaceid"), rs.getDate("start_time"), rs.getDate("end_time"), rs.getInt("pax"), rs.getString("status"), rs.getDate("creation_time"), rs.getDate("cancel_time"), rs.getString("cancel_user"), rs.getDate("record_date")));
            }
            rs.close();
            command.close();
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage(TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage(TDPLanguageConstants.ERR_UNKNOW));
        }
        return listBooking;
    }

    /**
     * Mètode que obte la lista de reserves - taula Bookings
     * Si userid es 0 es retornen les reserves de tots els usuaris
     * @return Lista de reserves
     * @param id Usuari, data inici i data fi
     * 
     */
    public List<Booking> GetAllBookings(String userid, String from, String to) throws EIssueException {

//        SimpleDateFormat textDateFormat = new SimpleDateFormat("dd-MM-yyyy");
//        Calendar calendar = Calendar.getInstance();
//        
//        String startTime = null;
//        String endTime = null;
//        
//        String todayStr =textDateFormat.format(calendar.getTime());
//        Date today =Calendar.getInstance().getTime();
//        
//        
//        try {
//            textDateFormat.parse(from.trim());
//            startTime=from;
//        } catch (ParseException ex) {
//            startTime = "1-1-1901";
//        }
//        
//        try {
//            textDateFormat.parse(to.trim());
//            endTime=to;
//        } catch (Exception ex) {
//            endTime = todayStr;
//        }        

        List<Booking> listBooking = new ArrayList();
        try {
            Statement command = this.mngBD.createCommand();
            String select;
            if (userid == "ALL") {
                select = "SELECT * FROM bookings WHERE start_time >= '" + from + "' AND end_time <= '" + to + "'";
            } else {
                select = "SELECT * FROM bookings WHERE userid ='" + userid + "' AND start_time >= '" + from + "' AND end_time <= '" + to + "'";
            }
            ResultSet rs = command.executeQuery(select);
            while (rs.next()) {
                listBooking.add(new Booking(rs.getInt("bookingid"), rs.getString("userid"), rs.getInt("spaceid"), rs.getDate("start_time"), rs.getDate("end_time"), rs.getInt("pax"), rs.getString("status"), rs.getDate("creation_time"), rs.getDate("cancel_time"), rs.getString("cancel_user"), rs.getDate("record_date")));
            }
            rs.close();
            command.close();
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage(TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage(TDPLanguageConstants.ERR_UNKNOW));
        }
        return listBooking;
    }

    /**
     * Mètode que obte una reserva - taula Bookings
     * @return Lista de reserva
     */
    public List<Booking> GetOneBooking(int bookingId) throws EIssueException {
        List<Booking> listBooking = new ArrayList();

        try {
            String select = null;
            select = "SELECT * FROM bookings WHERE bookingid=?";
            PreparedStatement command = this.mngBD.prepareCommand(select);
            command.setInt(1, bookingId);
            ResultSet rs = command.executeQuery(select);
            while (rs.next()) {
                listBooking.add(new Booking(rs.getInt("bookingid"), rs.getString("userid"), rs.getInt("spaceid"), rs.getDate("start_time"), rs.getDate("end_time"), rs.getInt("pax"), rs.getString("status"), rs.getDate("cretion_time"), rs.getDate("cancel_time"), rs.getString("cancel_user"), rs.getDate("record_date")));
            }
            rs.close();
            command.close();
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage(TDPLanguageConstants.ERR_STOP));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage(TDPLanguageConstants.ERR_UNKNOW));
        }
        return listBooking;
    }

    /**
     * Mètode que guarda dades (Update) un espai - taula Bookings
     * @return boolea
     */
    public boolean SaveBooking(Booking Booking) throws EIssueException {
        boolean PROCESS_STATE = false;
        try {

            String insertQuery = "INSERT INTO Bookings(bookingid, userid, spaceid, start_time, end_time, pax, status, creation_time, record_date) VALUES (?,?,?,?,?,?,?,?,?)";
            PreparedStatement command = this.mngBD.prepareCommand(insertQuery);
            command.setInt(1, Booking.getBookingid());
            command.setString(2, Booking.getUser());
            command.setInt(3, Booking.getSpace());
            command.setDate(4, new java.sql.Date(Booking.getStart_time().getTime()));
            command.setDate(5, new java.sql.Date(Booking.getEnd_time().getTime()));
            command.setInt(6, Booking.getPax());
            command.setString(7, Booking.getStatus());
            command.setDate(8, new java.sql.Date(System.currentTimeMillis()));
            command.setDate(9, new java.sql.Date(System.currentTimeMillis()));
            command.execute();
            command.close();
            PROCESS_STATE = true;
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage(TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage(TDPLanguageConstants.ERR_UNKNOW));
        }

        return PROCESS_STATE;
    }

    public int GetNewBookingId() throws EIssueException, SQLException {
        int recordId = 0;

        try {
            Statement command = this.mngBD.createCommand();
            String select = "SELECT Max(bookingid) FROM bookings";
            ResultSet rs = command.executeQuery(select);
            if (rs.next()) {
                recordId = rs.getInt(1) + 1;
            }
            rs.close();
            command.close();

        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage(TDPLanguageConstants.ERR_STOP));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage(TDPLanguageConstants.ERR_UNKNOW));
        }
        return recordId;
    }

    /**
     * Mètode que fa Update de un espai - taula Bookings
     * @return boolea
     */
    public boolean SaveBookingChanges(Booking Booking) throws EIssueException {
        boolean PROCESS_STATE = false;

        try {
            String updateQuery = "UPDATE Bookings SET userid=?, spaceid=?, start_time=?, end_time=?, pax=?, status=? WHERE bookingid=?";
            PreparedStatement command = this.mngBD.prepareCommand(updateQuery);

            command.setString(1, Booking.getUser());
            command.setInt(2, Booking.getSpace());
            command.setDate(3, new java.sql.Date(Booking.getStart_time().getTime()));
            command.setDate(4, new java.sql.Date(Booking.getEnd_time().getTime()));
            command.setInt(5, Booking.getPax());
            command.setString(6, Booking.getStatus());
            command.setInt(7, Booking.getBookingid());
            //command.setDate(8, new java.sql.Date(System.currentTimeMillis()));
            //command.setDate(11, new java.sql.Date(System.currentTimeMillis()));  
            command.execute();
            command.close();
            PROCESS_STATE = true;
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage(TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage(TDPLanguageConstants.ERR_UNKNOW));
        }

        return PROCESS_STATE;
    }

    /**
     * Mètode que fa dona de baixa/desactiva un espai - taula Bookings
     * @return boolea
     * author Tomeu
     */
    public boolean CancelBooking(int bookingId, String userName) throws EIssueException {
        boolean PROCESS_STATE = false;
        try {
            String updateQuery = "UPDATE Bookings SET status='Canceled', cancel_time=?, cancel_user=?  WHERE bookingid=?"; //tomeu
            PreparedStatement command = this.conexion.prepareStatement(updateQuery);
            command.setDate(1, new java.sql.Date(System.currentTimeMillis()));
            command.setString(2, userName);
            command.setInt(3, bookingId);
            command.executeUpdate();
            command.close();
            PROCESS_STATE = true;
            PayBooking(bookingId, CancelCharge(CalcPrice(bookingId)));
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage(TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage(TDPLanguageConstants.ERR_UNKNOW));
        }
//se debe aplicar la sancion
        return PROCESS_STATE;
    }

    /**
     * Mètode que Paga la reserva 
     * @return boolea
     */
    public boolean PayBooking(int bookingId, double amount) throws EIssueException {
        boolean PROCESS_STATE = false;
        try {
            String insertQuery = "INSERT INTO Payments(bookingid, payment_date, total_price, record_date)  VALUES (?, ?, ?, ?)";
            PreparedStatement command = this.mngBD.prepareCommand(insertQuery);
            command.setInt(1, bookingId);
            command.setDate(2, new java.sql.Date(System.currentTimeMillis()));
            command.setDouble(3, amount);
            command.setDate(4, new java.sql.Date(System.currentTimeMillis()));
            command.execute();
            command.close();
            PROCESS_STATE = true;
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage(TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage(TDPLanguageConstants.ERR_UNKNOW));
        }

        return PROCESS_STATE;
    }

    /**
     * Mètode que Calcula el preu d'una reserva
     * @return boolea
     */
    public double CancelCharge(double amount) throws EIssueException {
        return (double) amount * 0.10;
  
    }
    
    public double CalcPrice(int bookingId) throws EIssueException {
        double result = 0;
        double rSpace = 0;
        double rResource = 0;
        double spaceId = 0;
        try {

            String select = null;
            select = "select BR.bookingid, S.SpaceId, BR.resourceid, S.Price_hour as Price_hour_Space, S.Price_day as Price_day_Space, R.Price_hour as Price_hour_Res, R.Price_day as Price_day_Res, B.end_time,B.start_time,(B.end_time-B.start_time) as dif from booking_resources as BR inner join bookings as B on B.bookingid=BR.bookingid inner join spaces as S on S.spaceid=B.spaceId inner join all_resources as R on R.resourceid=BR.resourceid where BR.bookingid="+String.valueOf(bookingId) +" order by S.spaceid";
            Statement command = this.mngBD.createCommand();           
            ResultSet rs = command.executeQuery(select);
      
            while (rs.next()) {
                //Calculem preu espai, IF Controla que només sumem una vegada
                if (spaceId != rs.getInt("spaceid")) {
                    if (rs.getInt("dif") < 0) {
                        rSpace += rs.getDouble("Price_hour_Space");
                    } else {
                        rSpace += rs.getDouble("Price_day_Space");
                    }
                    spaceId=rs.getInt("spaceid");
                }
                //Calculem preu recursos
                if (rs.getInt("dif") < 0) {
                    rResource += rs.getDouble("Price_hour_Res");
                } else {
                    rResource += rs.getDouble("Price_day_Res");
                }
            }
            result = rSpace + rResource;
            rs.close();
            command.close();

        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage(TDPLanguageConstants.ERR_STOP));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage(TDPLanguageConstants.ERR_UNKNOW));
        }
        return result;
    }
    
    /**
     * Mètode que Calcula el preu d'una reserva
     * @return boolea
     */
    public int GetPriceBooking(int bookingId) throws EIssueException {
        int result = 0;
        try {

            String select = null;
            select = "select total_price from payments where bookingid="+String.valueOf(bookingId);
            Statement command = this.mngBD.createCommand();           
            ResultSet rs = command.executeQuery(select);
      
            if (rs.next()) {
                result = rs.getInt("total_price");
            }
            
            rs.close();
            command.close();

        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage(TDPLanguageConstants.ERR_STOP));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage(TDPLanguageConstants.ERR_UNKNOW));
        }
        return result;
    }
    
}
